/*
 * mysql_types testing types with MySQL and dbConnect API
 * Copyright (C) 2004 Johnathan Ingram, jingram@rogueware.org
 *
 * This library is free software; you can redistribute it and/or
 *   modify it under the terms of the GNU Lesser General Public
 *   License as published by the Free Software Foundation; either
 *   version 2.1 of the License, or (at your option) any later version.
 *
 *   This library is distributed in the hope that it will be useful,
 *   but WITHOUT ANY WARRANTY; without even the implied warranty of
 *   MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
 *   Lesser General Public License for more details.
 *
 *   You should have received a copy of the GNU Lesser General Public
 *   License along with this library; if not, write to the Free Software
 *   Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  US
 *
 * Note: This example requires the TypeTest table as defined in the SQL for MySQL
 */


#include <iostream>
#include <string>


#include "dbconn/dbconnect.h"

using namespace std;


void 
showTypeData(
      DbQueryVar &conn)
   throw(
      BaseException)
{   
   // Connection 1: List the contents of the table
   cout << "Result Set:" << endl;
   cout << "----------------------------------------- " << endl;
   cout << "Field\tasString\tasLong\tasUnsignedLong\tasFloat\tasDateTime\tasBoolean\tasBinary" << endl;
   cout << "-----\t--------\t------\t--------------\t-------\t----------\t---------\t--------" << endl;
   
   if (conn->eof())
      cout << "No rows in result set for this query" << endl;
   else
   {
      while (!conn->eof())
      {
         conn->fetchNext();
         
         // Output each field and do translation
         for(int i=0; i<conn->fieldCount(); i++)
         {
            if (conn->getFieldByColumn(i)->isNULL())
               cout << "NULL" << "\t";
            else
            {
               cout << conn->getFieldByColumn(i)->name() << "\t";
               
               // View the JDate class for what can be done with Date/Time values from asDateTime
               // LOB/BLOB values will be hex encoded when obtained with asString                              
               // LOB/BLOB will return a pointer to the binary data in mememory 
               //   Can use cout << conn->getFieldByColumn(i)->getSize() to obtain the size of the LOB/BLOB
               cout << conn->getFieldByColumn(i)->asString() << "\t";
               cout << conn->getFieldByColumn(i)->asLong() << "\t";
               cout << conn->getFieldByColumn(i)->asUnsignedLong() << "\t";
               cout << conn->getFieldByColumn(i)->asFloat() << "\t";
               cout << conn->getFieldByColumn(i)->asDateTime().asString() << "\t";
               cout << conn->getFieldByColumn(i)->asBoolean() << "\t";               
               cout << conn->getFieldByColumn(i)->asBinary() << "\t";
               cout << endl;
            }
         }         
      }
   }
   cout << endl;   
}



int 
main(
      int argc, char** argv)
{
   DbConnectionVar driver;
   DbQueryVar      conn;
   string          sqlQuery;
      
   try
   {
      driver = new DbConnection(DbConnection::MYSQL, "./dbconnect.cfg");

      // Connect to the database. 
      driver->connect("dbconnect", "letmein", "dbConnectDB", "localhost");

      // Get a query connection object
      conn = driver->requestQueryConnection();

      
      // Test 1: MySQL Integer Types
      cout << endl << endl;
      cout << "TEST 1: MySQL Integer Types" << endl;
      cout << "---------------------------" << endl;
      
      sqlQuery = 
         "SELECT "
         "  t_TINYINT, t_SMALLINT, t_MEDIUMINT, t_INT, t_BIGINT "
         "FROM "
         "  TypeTest "
         "WHERE "
         "  id = :id ";
      conn->command(sqlQuery);
      conn->bindParam("id")->setLong(1);
      conn->execute();
      showTypeData(conn);
      

      // Test 2: MySQL Unsigned Integer Types
      cout << endl << endl;
      cout << "TEST 2: MySQL Unsigned Integer Types" << endl;
      cout << "---------------------------" << endl;
      
      sqlQuery = 
         "SELECT "
         "  t_TINYINT_UNSIGNED, t_SMALLINT_UNSIGNED, t_MEDIUMINT_UNSIGNED, t_INT_UNSIGNED, t_BIGINT_UNSIGNED "
         "FROM "
         "  TypeTest "
         "WHERE "
         "  id = :id ";
      conn->command(sqlQuery);
      conn->bindParam("id")->setLong(2);
      conn->execute();
      showTypeData(conn);


      // Test 3: MySQL Floating Point Types
      cout << endl << endl;
      cout << "TEST 3: MySQL Floating Point Types" << endl;
      cout << "---------------------------" << endl;
      
      sqlQuery = 
         "SELECT "
         "  t_FLOAT, t_DOUBLE, t_DOUBLE_PRECISION, t_DECIMAL "
         "FROM "
         "  TypeTest "
         "WHERE "
         "  id = :id ";
      conn->command(sqlQuery);
      conn->bindParam("id")->setLong(3);
      conn->execute();
      showTypeData(conn);


      // Test 4: MySQL Date & Time Types
      cout << endl << endl;
      cout << "TEST 4: MySQL Date & Time Types" << endl;
      cout << "---------------------------" << endl;
      
      sqlQuery = 
         "SELECT "
         "  t_DATE, t_DATETIME, t_TIMESTAMP, t_TIME, t_YEAR "
         "FROM "
         "  TypeTest "
         "WHERE "
         "  id = :id ";
      conn->command(sqlQuery);
      conn->bindParam("id")->setLong(4);
      conn->execute();
      showTypeData(conn);


      // Test 5: MySQL Character (String) Types
      cout << endl << endl;
      cout << "TEST 5: MySQL Character (String) Types" << endl;
      cout << "---------------------------" << endl;
      
      sqlQuery = 
         "SELECT "
         "  t_CHAR, t_VARCHAR "
         "FROM "
         "  TypeTest "
         "WHERE "
         "  id = :id ";
      conn->command(sqlQuery);
      conn->bindParam("id")->setLong(5);
      conn->execute();
      showTypeData(conn);


      // Test 6: MySQL LOB Types
      cout << endl << endl;
      cout << "TEST 6: MySQL LOB Types" << endl;
      cout << "---------------------------" << endl;
      
      sqlQuery = 
         "SELECT "
         "  t_TINYBLOB, t_BLOB, t_LONGBLOB "
         "FROM "
         "  TypeTest "
         "WHERE "
         "  id = :id ";
      conn->command(sqlQuery);
      conn->bindParam("id")->setLong(6);
      conn->execute();
      showTypeData(conn);


      // Test 7: MySQL Set/Enum Types
      cout << endl << endl;
      cout << "TEST 7: MySQL Set/Enum Types" << endl;
      cout << "---------------------------" << endl;
      
      sqlQuery = 
         "SELECT "
         "  t_ENUM, t_SET "
         "FROM "
         "  TypeTest "
         "WHERE "
         "  id = :id ";
      conn->command(sqlQuery);
      conn->bindParam("id")->setLong(7);
      conn->execute();
      showTypeData(conn);

   }
   catch(BaseException &ex)
   {
      // Only need to catch a single exception. Can use 'name' etc to determine the actual exception
      cout << "DbConnect Exception: " << ex.name << endl 
           << "  " << ex.code << " : " << ex.description << endl;
   }
   catch(...)
   {
      cout << "An Unknown exception has been trapped!\n" << endl;
   }
   cout << endl;
        
   // The 'conn' and 'driver' objects are smart pointers and will cleanup as soon as they go out of scope      

   return 0;
}
